create database tbl
on
( name='tbl',
  filename='D:\tbl.mdf'


)
log on
( name='tbl_log',
  filename='D:\tbl_log.ldf'



)
go
use tbl
create table tbl_card
(
    id varchar(20) primary key  not null,
	passWord nvarchar(20) not null,
	balance int not null ,
	userName nvarchar(20) not null


)
create table tbl_computer
(
     id varchar(20) primary key not null  ,
	 onUse varchar(2) check(onUse in ('0','1')) not null,
	 note nvarchar(20)



 )
 create table tbl_record
 (  
      id varchar(20) primary key not null,
	  cardId  varchar(20) references tbl_card(id),
	  ComputerId  varchar(20) references tbl_computer(id),
	  beginTime datetime ,
	  endTime datetime,
	  fee int 


 
 
 )
 insert into tbl_card values ('0023_ABC','555','98','蠟엊'),('0025_bbd','abe','300','聾에'),('0036_CCD','부진','100','부진'),('0045_YGR','0045_YGR','58','聯駱'),('0078_RJV','55885fg','600','叫헤'),('0089_EDE','zhang','134','蠟엎')
 select * from tbl_card
 insert into tbl_computer values('02','0','25555'),('03','1','55555'),('04','0','66666'),('05','1','88888'),('06','0','688878'),('B01','0','558558')
 select * from tbl_computer
 insert into tbl_record values('23','0078_RJV','B01','2007-07-15 19:00:00','2007-07-15 21:00:00','20'),
 ('34','0025_bbd','02','2006-12-25 18:00:00','2006-12-25 22:00:00','23'),
 ('45','0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
 ('46','0023_ABC','03','2006-12-22 15:26:00','2006-12-22 22:55:00','6'),
 ('47','0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
 ('48','0023_ABC','03','2007-01-06 15:26:00','2007-01-06 22:55:00','6'),
 ('55','0023_ABC','03','2006-07-21 15:26:00','2006-07-21 22:55:00','50'),
 ('64','0045_YGR','04','2006-12-24 18:00:00','2006-12-24 22:00:00','30'),
 ('65','0025_bbd','02','2006-12-28 18:00:00','2006-12-28 22:00:00','23'),
 ('98','0025_bbd','02','2006-12-26 18:00:00','2006-12-26 22:00:00','23')
 select * from tbl_record

 select cardId,userName,ComputerId,beginTime,endTime,fee from tbl_record inner join tbl_card on tbl_record.cardId=tbl_card.id where userName='蠟엊' order by fee desc

 select ComputerId,COUNT(cardId),SUM(fee) from tbl_record group by ComputerId

 select cardId,SUM(fee) from tbl_record left join tbl_card on tbl_record.cardId=tbl_card.id group by cardId

 select tbl_card.id,userName from tbl_card left join  tbl_record on  tbl_record.cardId=tbl_card.id where fee is null

 select a.* from tbl_card a,tbl_card b where a.passWord=b.userName 

 select top 1 ComputerId,count(id) from tbl_record group by ComputerId order by count(id)desc

 select cardId,userName,ComputerId,beginTime,endTime,fee from tbl_record inner join tbl_card on tbl_record.cardId=tbl_card.id where cardId like '%_ABC'

 select cardId,userName,ComputerId,beginTime,endTime,fee from tbl_record inner join tbl_card on tbl_record.cardId=tbl_card.id where datepart(weekday,endTime)=1 or datepart(weekday,endTime)=7

 select cardId,userName,ComputerId,beginTime,endTime,fee from tbl_record inner join tbl_card on tbl_record.cardId=tbl_card.id  where datediff(HH,beginTime,endTime) >12

 select cardId,userName,ComputerId,beginTime,endTime,fee from tbl_record inner join tbl_card on tbl_record.cardId=tbl_card.id
 except
 select top 3 cardId,userName,ComputerId,beginTime,endTime,fee from tbl_record inner join tbl_card on tbl_record.cardId=tbl_card.id order by fee desc